package StudentAndManagerTest;

import java.sql.*;
import java.util.Date;

public class BookManagementSystem {
    // 数据库连接信息
    private static final String URL = "jdbc:mysql://localhost:3306//ebook";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    // 获取数据库连接
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    // 查询图书表中所有图书信息
    public static void getAllBooks() {
        String sql = "SELECT * FROM book";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String press = rs.getString("press");
                String author = rs.getString("author");
                int pagination = rs.getInt("pagination");
                double price = rs.getDouble("price");
                Date uploadTime = rs.getDate("uploadtime");
                System.out.printf("ID: %d, 书名: %s, 出版社: %s, 作者: %s, 页数: %d, 价格: %.2f, 上架时间: %s\n",
                        id, name, press, author, pagination, price, uploadTime);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 新增 1 条图书信息到图书表中，图书上架时间为当前系统时间
    public static void addBook(String name, String press, String author, int pagination, double price) {
        String sql = "INSERT INTO book (name, press, author, pagination, price, uploadtime) VALUES (?,?,?,?,?,?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, press);
            pstmt.setString(3, author);
            pstmt.setInt(4, pagination);
            pstmt.setDouble(5, price);
            pstmt.setDate(6, new java.sql.Date(new java.util.Date().getTime()));
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("图书添加成功");
            } else {
                System.out.println("图书添加失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 修改图书表中图书名称为 Java 基础入门（第 2 版）的图书价格为 69.0
    public static void updateBookPrice() {
        String sql = "UPDATE book SET price = 69.0 WHERE name = 'Java 基础入门（第 2 版）'";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            int rows = stmt.executeUpdate(sql);
            if (rows > 0) {
                System.out.println("图书价格修改成功");
            } else {
                System.out.println("未找到对应图书，修改失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 删除图书表中图书编号为 1 的图书信息
    public static void deleteBook() {
        String sql = "DELETE FROM book WHERE id = 1";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            int rows = stmt.executeUpdate(sql);
            if (rows > 0) {
                System.out.println("图书删除成功");
            } else {
                System.out.println("未找到对应图书，删除失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
// 1. 查询所有图书信息
        System.out.println("查询所有图书信息：");
        getAllBooks();

// 2. 新增 1 条图书信息
        System.out.println("\n新增图书信息：");
        addBook("新图书", "新出版社", "新作者", 300, 49.99);

// 3. 修改图书价格
        System.out.println("\n修改图书价格：");
        updateBookPrice();

// 4. 删除图书信息
        System.out.println("\n删除图书信息：");
        deleteBook();

// 再次查询所有图书信息，查看操作结果
        System.out.println("\n操作后的图书信息：");
        getAllBooks();
    }
}